package com.api.weavernorth.salary;


import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import com.weavernorth.util.Logger4jUtil;
import com.weavernorth.util.dateUtils;
import org.apache.log4j.Logger;
import weaver.conn.RecordSet;
import weaver.general.Util;

import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.ws.rs.GET;
import javax.ws.rs.Path;
import javax.ws.rs.Produces;
import javax.ws.rs.core.Context;
import javax.ws.rs.core.MediaType;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.Calendar;
import java.util.Date;

/**
 * @program: ecology_erdos
 * @description:查询建模表中店铺人员销售信息
 * @author: zm
 * @create: 2020-01-15
 **/
@Path("/weavernorth/salary")
public class V8ModeMess extends HttpServlet {
//    private static Log log = LogFactory.getLog(V8ModeMess.class);
private Logger log = Logger4jUtil.getLoggerByName("/action/api/V8ModeMess/");
    /**
     * 终端店铺销售提成申请表流程获取v8信息
     *
     * @param request
     * @param response
     * @return
     */
    @GET
    @Path("/getV8mess")
    @Produces(MediaType.TEXT_PLAIN)
    public String getV8mess(@Context HttpServletRequest request, @Context HttpServletResponse response) {
        //数据库操作
        RecordSet rs = new RecordSet();
        //返回信息
        JSONArray jsonArray = new JSONArray();
        //返回所有数据
        JSONObject json = new JSONObject();
        //销售总流水
        JSONObject jsonMain = new JSONObject();

        //部门id
        String strDpbh = Util.null2String(request.getParameter("dpbh"));
        //操作日期
        String strCzrq = Util.null2String(request.getParameter("sqsj"));
        // String preMonthDay = getPreMonth(strCzrq);
        String preMonthDay = strCzrq;
        //操作日期年月
        String strCzrqYearMonth = "";
        //小程序正价汇总
        Double douXcxzjhz = 0.0;
        //小程序折价汇总
        Double douXcxzhejhz = 0.0;
        //得到操作日期的年份和月
        if (!"".equals(preMonthDay)) {
            strCzrqYearMonth = preMonthDay.split("-")[0] + preMonthDay.split("-")[1];

        }
        //查询建模表信息
        // String sql = "select * from uf_xsyjhzb where dpbm=? and xzrq=?  and  ((gwdm='01' and sfzls=0) or gwdm='02')";
        String sql = "SELECT\n" +
                "\tux.ygxm,\n" +
                "\tux.ygmc,\n" +
                "\tux.ygh,\n" +
                "\tux.yrw,\n" +
                "\tux.zjxs,\n" +
                "\tux.sszjls,\n" +
                "\tux.zhejxs,\n" +
                "\tux.sszhjls,\n" +
                "\tux.gwmc,\n" +
                "\tux.zwmc,\n" +
                "\tux.gwdm,\n" +
                "\tux.sfzls,\n" +
                "\tux.tgzjls,\n" +
                "\tux.tgzjlss,\n" +
                "\tux.wxzjls,\n" +
                "\tux.wxzjlss,\n" +
                "\tuz.xcxzjxs,\n" +
                "\tuz.xcxzjxss,\n" +
                "\tux.xszls,\n" +
                "\tux.ygdpbm,\n" +
                "\tux.zyzj,\n" +
                "\tux.zyzhej,\n" +
                "\tux.zyzls,\n" +
                "\tux.sszls,\n" +
                "  v.xsrwe as yrwNew\n" +
                "FROM\n" +
                "\tuf_xsyjhzb ux\n" +
                "\tLEFT JOIN uf_yrwmx_view v ON ux.dpbm=v.dpbh AND ux.xzrq=v.xzrq\n" +
                "\tLEFT JOIN uf_zdxcxxsyj uz ON ux.dpbm = uz.sapdpbm \n" +
                "\tAND ux.ygh = uz.ygbh \n" +
                "\tAND ux.xzrq = uz.xsqj" +
                "  where ux.dpbm=? and ux.xzrq=? order by ux.gwdm ,ux.ygh";
        log.info("查询建模表中店铺信息:" + sql + ",strDpbh:" + strDpbh + ",strCzrqYearMonth:" + strCzrqYearMonth);
        rs.executeQuery(sql, strDpbh, strCzrqYearMonth);
        while (rs.next()) {
            JSONObject jsonObject = new JSONObject();
            //员工id
            String strUserid = Util.null2String(rs.getString("ygxm"));
            jsonObject.put("userid", strUserid);
            //员工名称
            String strUserName = Util.null2String(rs.getString("ygmc"));
            jsonObject.put("username", strUserName);
            //员工编码
            String strWorkcode = Util.null2String(rs.getString("ygh"));
            jsonObject.put("workcode", strWorkcode);
            //非19/20月任务
            String strMonthTask = Util.null2String(rs.getString("yrwNew"));
            jsonObject.put("monthtask", strMonthTask);
            //非岗位19/20月任务
            String strMonthTask2 = Util.null2String(rs.getString("yrw"));
            jsonObject.put("monthtask2", strMonthTask2);
            //正价销售
            String strValSales = Util.null2String(rs.getString("zjxs"));
            jsonObject.put("valsales", strValSales);
            //正价销售(含虚销)
            String strValSales_x = Util.null2String(rs.getString("sszjls"));
            jsonObject.put("valsales_x", strValSales_x);
            //折价销售
            String strDisSales = Util.null2String(rs.getString("zhejxs"));
            jsonObject.put("dissales", strDisSales);
            //折价销售(含虚销)
            String strDisSales_x = Util.null2String(rs.getString("sszhjls"));
            jsonObject.put("dissales_x", strDisSales_x);
            //岗位id
            String strGwmc = Util.null2String(rs.getString("gwmc"));
            jsonObject.put("jobid", strGwmc);
            //岗位名称
            String strJobName = Util.null2String(rs.getString("zwmc"));
            jsonObject.put("jobname", strJobName);

            //岗位代码
            String strGwdm = Util.null2String(rs.getString("gwdm"));
            jsonObject.put("gwdm", strGwdm);
            //是否总流程
            String strSfzls = Util.null2String(rs.getString("sfzls"));
            //团购正价
            String strTgzj = Util.null2String(rs.getString("tgzjls"));
            jsonObject.put("tgzj", strTgzj);
            //团购折价
            String strTgzhej = Util.null2String(rs.getString("tgzjlss"));
            jsonObject.put("tgzhej", strTgzhej);
            //微销正价
            String strWxzj = Util.null2String(rs.getString("wxzjls"));
            jsonObject.put("wxzj", strWxzj);
            //微销折价
            String strWxzhej = Util.null2String(rs.getString("wxzjlss"));
            jsonObject.put("wxzhej", strWxzhej);

            //小程序正价销售
            Double douXcxzjxs = Util.getDoubleValue(rs.getString("xcxzjxs"), 0.0);
            jsonObject.put("xcxzjxs", douXcxzjxs);
            douXcxzjhz = douXcxzjhz + douXcxzjxs;
            //小程序折价销售
            Double douXcxzhejxs = Util.getDoubleValue(rs.getString("xcxzjxss"), 0.0);
            jsonObject.put("xcxzhejxs", douXcxzhejxs);
            douXcxzhejhz = douXcxzhejhz + douXcxzhejxs;

            //销售总流水
            String strXszls = Util.null2String(rs.getString("xszls"));
            jsonObject.put("xszls", strXszls);

            //销售总流水(含虚销)
            String strXszls_x = Util.null2String(rs.getString("sszls"));
            jsonObject.put("xszls_x", strXszls_x);

            //员工所属店铺编码
            String strYgssdpbm = Util.null2String(rs.getString("ygdpbm"));
            jsonObject.put("ygdpbm", strYgssdpbm);
            //如果为总流水,放入单独的json里,表单显示在主表里
            if ("0".equals(strSfzls)) {
                //支援正价总计
                String strZyzj = Util.null2String(rs.getString("zyzj"));
                jsonObject.put("zyzls", strZyzj);
                //支援折价总计
                String strZzyzj = Util.null2String(rs.getString("zyzhej"));
                jsonObject.put("zyzhej", strZzyzj);

                //支援总流水
                String strZyzls = Util.null2String(rs.getString("zyzls"));
                jsonObject.put("zyzls", strZyzls);
                jsonMain = jsonObject;
            }
            jsonMain.put("xcxzjhz", douXcxzjhz);

            jsonMain.put("xcxzhejhz", douXcxzhejhz);
            //员工编号不为空时,添加到明细
            if (!"".equals(strWorkcode)) {
                jsonArray.add(jsonObject);
            }
            //返回数据
        }
        json.put("maindata", jsonMain);
        json.put("detaildata", jsonArray);

        return json.toString();
    }

    /**
     * 当完成比改变时,获取正价提率等信息
     *
     * @param request
     * @param response
     * @return
     */
    @GET
    @Path("/percenComplete")
    @Produces(MediaType.TEXT_PLAIN)
    public String percenComplete(@Context HttpServletRequest request, @Context HttpServletResponse response) {
        //数据库操作
        RecordSet rs = new RecordSet();
        RecordSet rsWcl = new RecordSet();
        //返回值
        JSONArray jsonArray = new JSONArray();
        //店铺编码
        String strDpbh = Util.null2String(request.getParameter("dpbh"));
        //完成比
        Double souWcb = Util.getDoubleValue(request.getParameter("wcbVal"), 0.0);
        //岗位编码
        String strJobCode = Util.null2String(request.getParameter("jobcode"));
        //申请时间,提率需要用前一个月去对比
        String sqsjVal = Util.null2String(request.getParameter("sqsjVal"));
        //店铺完成率
        Double douDpwcl = Util.getDoubleValue(request.getParameter("dpwcl"), 0.0);
        //得到申请时间的前一个月
        // String preMonthDay = getPreMonth(sqsjVal);

        String preMonthDay = sqsjVal;
        //店经理个人完成率
        Double douMangeRate = 0.0;
        //首先得到店经理的个人完成率
        String sqlWcl = "select djlgrwcl from uf_xstcgzb ux,uf_xstcgzb_dt1 uxd,uf_dpsygzgxb ud where ud.gzbh = ux.id and ux.id=uxd.mainid" +
                " and ud.dpbh=? and uxd.gwdm='01' and ud.yxksrq<=? and ud.yxjsrq>=?";
        log.info("得到店经理的个人完成率:" + sqlWcl + ",strDpbh:" + strDpbh + ",preMonthDay:" + preMonthDay);
        rsWcl.executeQuery(sqlWcl, strDpbh, preMonthDay, preMonthDay);
        if (rsWcl.next()) {
            douMangeRate = Util.getDoubleValue(rsWcl.getString("djlgrwcl"), 0.0);
        }
        log.info("店经理的个人完成率:" + douMangeRate);
        //如果店经理的销售完成率大于规则表维护的完成率,则取店铺的销售完成率
        if (souWcb >= douMangeRate && "01".equals(strJobCode)) {
            souWcb = douDpwcl;
        } else if (souWcb < douMangeRate && "01".equals(strJobCode)) {
            souWcb = 0.0;
        }
        log.info("当前销售完成比:" + souWcb);
        //查询提率和保底金额,规则表和规则关系表联查,在有效期内且完成比在销售完成率上下线之间
        String sql = "select * from uf_xstcgzb ux,uf_xstcgzb_dt1 uxd,uf_dpsygzgxb ud where ud.gzbh = ux.id and ux.id=uxd.mainid" +
                " and ud.dpbh=? and uxd.gwdm=? and ud.yxksrq<=? and ud.yxjsrq>=? and uxd.xswclxx<=? and uxd.xswclsx>?";
        log.info("得到提率和保底金额:" + sql + ",strDpbh:" + strDpbh + ",strJobCode:" + strJobCode + ",preMonthDay:" + preMonthDay + ",souWcb:" + souWcb);
        rs.executeQuery(sql, strDpbh, strJobCode, preMonthDay, preMonthDay, souWcb, souWcb);
        while (rs.next()) {
            JSONObject jsonObject = new JSONObject();
            //提率
            String strTl = Util.null2String(rs.getString("tl"));
            jsonObject.put("tl", strTl);
            log.info("得到提率:" + strTl);
            String strSfzj = Util.null2String(rs.getString("sfzj"));
            jsonObject.put("sfzj", strSfzj);
            log.info("是否正价:" + strSfzj);
            //保底金
            String strBdje = Util.null2String(rs.getString("bdje"));
            jsonObject.put("bdje", strBdje);
            //淡旺季编号
            String strDwj = Util.null2String(rs.getString("dwj"));
            //得到不需要的数据的id
            String strDw = getDwSeson(strDwj, preMonthDay);
            log.info("淡旺季返回的值:" + strDw);
            //根据前一个月判断淡旺季,然后得到返回的当前季节和全年的数据
            if (("".equals(strDw))) {
                jsonArray.add(jsonObject);
            }

        }
        //判断数组是否为空,否则返回""
        if (jsonArray != null) {
            return jsonArray.toString();
        } else {
            return "";
        }

    }


    /**
     * 得到淡旺季标识
     *
     * @param strDate 申请时间的前一个月
     */
    public String getDwSeson(String strDwId, String strDate) {
        RecordSet rs = new RecordSet();
        //得到年份
        int intYear = Integer.parseInt(strDate.split("-")[0]);
        //当前日期
        Date date = new Date();
        SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd");
        String strCurr = format.format(date);
        //当前月份
        String strCurrMonth = strCurr.split("-")[1];

        //返回信息
        String strBs = "";
        //在有效期内的
        String sql = "select * from uf_dwjczb where id=? and  yxksrq<=? and yxjsrq>=?";
        //判断是否需要全年的编码
        log.info("查询淡旺季:" + sql + ",id:" + strDwId + ",strDate:" + strDate);
        rs.executeQuery(sql, strDwId, strDate, strDate);
        while (rs.next()) {
            //开始月份
            int intKsyf = Util.getIntValue(rs.getString("ksyf"));
            //开始年月日
            String strBeginDate = String.valueOf(intYear) + "-" + String.format("%02d", Integer.valueOf(intKsyf)) + "-01";
            //结束月份
            int intJsyf = Util.getIntValue(rs.getString("jsyf"));
            //结束日期
//            String strEndDate = String.valueOf(intYear) + "-" + String.format("%02d", Integer.valueOf(intJsyf)) + "-01";
            String strEndDate = dateUtils.getLastDayOfMonth(intYear,intJsyf);
            //淡旺季标识
            String strDwj = Util.null2String(rs.getString("dwj"));
            //是否跨年
            String strSfkn = Util.null2String(rs.getString("sfkn"));
            log.info("是否跨年:" + strSfkn);
            //如果跨年,则结束月份年份加1
            if ("1".equals(strSfkn)) {
                log.info("开始月份:" + intKsyf);
                log.info("当前月份:" + strCurrMonth);
                log.info("年份:" + intYear);
                //跨年的话,再比较当前月份是否小于开始月份
                if (intKsyf > Integer.parseInt(strCurrMonth)) {
                    strBeginDate = String.valueOf(intYear - 1) + "-" + String.format("%02d", Integer.valueOf(intKsyf)) + "-01";
                } else {
//                    strEndDate = String.valueOf(intYear + 1) + "-" + String.format("%02d", Integer.valueOf(intJsyf)) + "-01";
                      strEndDate = dateUtils.getLastDayOfMonth(intYear,intJsyf);
                }
            }
            log.info("开始日期:" + strBeginDate + ",结束日期:" + strEndDate + ",传入标识:" + strDate);
            //返回不在当前月份的编号
            if (!(strBeginDate.compareTo(strDate) <= 0 && strEndDate.compareTo(strDate) >= 0)) {
                log.info("返回淡旺季标识:" + strDwId);
                return strDwId;
            }

        }

        return strBs;
    }

    /**
     * 【品牌】区域经理提成申请（基数方式）：销售总流水，小程序总流水，月任务
     * @return
     */

    @GET
    @Path("/retailmess")
    @Produces(MediaType.TEXT_PLAIN)
    public String getRetailMess(@Context HttpServletRequest request, @Context HttpServletResponse response) {
        //用户id
        String strUserid = Util.null2String(request.getParameter("userid"));
        //查询的字段名称
        String strZdmc = Util.null2String(request.getParameter("zdmc"));
        //操作日期
        String strCzrq = Util.null2String(request.getParameter("czrq"));
        //获取当前年月
        //  SimpleDateFormat format = new SimpleDateFormat("yyyy-MM");
        //分公司
        String strFgsVal = Util.null2String(request.getParameter("fgsVal"));
        String strYearMonth = strCzrq.split("-")[0] + strCzrq.split("-")[1];
        //是否按全地区销售业绩计算
        String strSfqdq = Util.null2String(request.getParameter("sfaqdqxsyjjs"));

        //数据库操作
        RecordSet rs = new RecordSet();
        RecordSet rsXcx = new RecordSet();
        RecordSet rsYrw=new RecordSet();
        JSONObject json = new JSONObject();
        //小程序总流水
        StringBuffer strXcxSql = new StringBuffer("select sum(xcxxszls) xcxxszls from uf_dpxx ud,uf_zdxcxxsyj uz where ud.dpsapid =uz.sapdpbm and  ','||ud." + strZdmc + "||','  like   '%," + strUserid + ",%' and uz.xsqj=? and ud.fgs=?");
        rsXcx.executeQuery(strXcxSql.toString(),strYearMonth,strFgsVal);
        log.info("获取小程序总流水Sql："+"select sum(xcxxszls) xcxxszls from uf_dpxx ud,uf_zdxcxxsyj uz where ud.dpsapid =uz.sapdpbm and  ','||ud." + strZdmc + "||','  like   '%," + strUserid + ",%' and uz.xsqj='"+strYearMonth+"' and ud.fgs='"+strFgsVal+"'");
        //获取区经管辖的店铺下的销售业绩汇总
        StringBuffer strDpsql = new StringBuffer("select  sum(sszls) xlzls  from uf_dpxx ud,uf_xsyjhzb ux where ud.dpsapid=ux.dpbm and  ','||ud." + strZdmc + "||','  like   '%," + strUserid + ",%' and ux.sfzls='0' and ux.xzrq=? and ud.fgs=?");
        rs.executeQuery(strDpsql.toString(), strYearMonth, strFgsVal);
        log.info("获取区经管辖的店铺下的销售业绩汇总Sql:"+"select  sum(sszls) xlzls  from uf_dpxx ud,uf_xsyjhzb ux where ud.dpsapid=ux.dpbm and  ','||ud." + strZdmc + "||','  like   '%," + strUserid + ",%' and ux.sfzls='0' and ux.xzrq='"+strYearMonth+"' and ud.fgs='"+strFgsVal+"'");
        //根据人员得到所管辖的店铺及店铺的月任务
        StringBuffer strYrwsql = new StringBuffer("SELECT v.nf,v.yf,v.xzrq,SUM(v.XSRWE) as yrwhz FROM uf_dpxx ud,UF_YRWMX_VIEW v WHERE  ud.dpsapid=v.DPBH and  ','||ud." + strZdmc + "||','  like   '%," + strUserid + ",%' and v.xzrq=?  GROUP BY v.nf,v.yf,v.xzrq");
        StringBuffer strYrwsql2 = new StringBuffer("SELECT v.nf,v.yf,v.xzrq,SUM(v.XSRWE) as yrwhz FROM uf_dpxx ud,UF_YRWMX_VIEW v WHERE  ud.dpsapid=v.DPBH and  ','||ud." + strZdmc + "||','  like   '%," + strUserid + ",%' and v.xzrq=? ");
        rsYrw.executeQuery(strYrwsql.toString(),strYearMonth);
        log.info("获取店铺月任务Sql:"+"SELECT v.nf,v.yf,v.xzrq,SUM(v.XSRWE) as yrwhz FROM uf_dpxx ud,UF_YRWMX_VIEW v WHERE  ud.dpsapid=v.DPBH and  ','||ud." + strZdmc + "||','  like   '%," + strUserid + ",%' and v.xzrq='"+strYearMonth+"'  GROUP BY v.nf,v.yf,v.xzrq");
        if ("1".equals(strSfqdq)) {
            //销售业绩归属人
            String strXsyjgsr = Util.null2String(request.getParameter("xsyjgsr"));
            if (!"".equals(strXsyjgsr)) {
                strDpsql.append(" and ");
                strXcxSql.append(" and ");
                strYrwsql2.append(" and ");
                String[] arrGsr = strXsyjgsr.split(",");
                for (int i = 0; i < arrGsr.length; i++) {
                    if(i==0){
                        strDpsql.append("(");
                        strXcxSql.append("(");
                        strYrwsql2.append("(");
                    }
                    strDpsql.append(" ','|| ud.lsjl ||','like '%," + arrGsr[i] + ",%' ");
                    strXcxSql.append(" ','|| ud.lsjl ||',' like '%," + arrGsr[i] + ",%'");
                    strYrwsql2.append(" ','|| ud.lsjl ||',' like '%," + arrGsr[i] + ",%'");
                    if (i != arrGsr.length - 1) {
                        strDpsql.append(" or ");
                        strXcxSql.append(" or ");
                        strYrwsql2.append(" or ");
                    } else {
                        strDpsql.append(" )");
                        strXcxSql.append(" )");
                        strYrwsql2.append(" )");
                    }

                }
            }
            log.info("销售业绩归属人:" + strXsyjgsr);
            rs.executeQuery(strDpsql.toString(), strYearMonth, strFgsVal);
            rsXcx.executeQuery(strXcxSql.toString());
            strYrwsql2.append(" GROUP BY v.nf,v.yf,v.xzrq");
            rsYrw.executeQuery(strYrwsql2.toString(),strYearMonth);

        }
        log.info("小程序总流水:"+strXcxSql.toString());
        log.info("获取销售总任务和总流水:" + strDpsql + ",strUserid:" + strUserid + ",strYearMonth:" + strYearMonth + ",strFgsVal:" + strFgsVal);
        log.info("获取销售总任务和总流水:" + strYrwsql);
        if (rs.next()) {
            json.put("xlzls", Util.getDoubleValue(rs.getString("xlzls"), 0));
        }
        while (rsYrw.next()){
            json.put("yrw", Util.getDoubleValue(rsYrw.getString("yrwhz"), 0));
        }
        if(rsXcx.next()){
            json.put("xcxxszls", Util.getDoubleValue(rsXcx.getString("xcxxszls"), 0));
        }
        if (json != null) {
            return json.toString();
        } else {
            return "";
        }
    }

    /**
     * 得到零售经理的销售提成
     *
     * @return 奖金金额
     */

    @GET
    @Path("/getTcje")
    @Produces(MediaType.TEXT_PLAIN)
    public String getTcje(@Context HttpServletRequest request, @Context HttpServletResponse response) {
        //数据库操作
        RecordSet rs = new RecordSet();
        //奖金金额
        String strJjje = "";
        //分公司id
        String strSubcomId = Util.null2String(request.getParameter("fgsVal"));
        //完成率
        Double douWcl = Util.getDoubleValue(request.getParameter("wclVal"));
        //分公司月任务总和
        Double douFgwrw = Util.getDoubleValue(request.getParameter("fgsrwVal"));// / 10000;
        //操作日期
        String strSqsj = Util.null2String(request.getParameter("czrqVal"));
        //查询有效提成
        String sql = "select * from uf_sygzjlblsjl us,uf_jjdfs_lsjl uj,uf_jjdfs_lsjl_dt1 ujd where us.gzbh = uj.id  and uj.id =ujd.mainid " +
                " and  us.yxksrq<='"+strSqsj+"' and us.yxjsrq>='"+strSqsj+"'   and ujd.rwdwxx<="+douFgwrw+" and ujd.rwdwsx>"+douFgwrw+"" +
                "   and ujd.wcjjdxx<="+douWcl+" and ujd.wcjjdsx>"+douWcl+" ";
        if(!"".equals(strSubcomId)){
            sql+="and us.fgs='"+strSubcomId+"'";
        }
        log.info("查询零售经理的销售金额:" + sql + ";分公司id:" + strSubcomId + ";操作日期:" + strSqsj + ";分公司任务总和:" + douFgwrw + ";完成率:" + douWcl);
        rs.executeQuery(sql);
        while (rs.next()) {
            strJjje = Util.null2String(rs.getString("jjjey"));
            log.info("提成金额："+strJjje);

        }
        return strJjje;

    }

    /**
     * 得到区域经理的销售提成
     *
     * @return 奖金金额
     */

    @GET
    @Path("/getMangerTcje")
    @Produces(MediaType.TEXT_PLAIN)
    public String getMangerTcje(@Context HttpServletRequest request, @Context HttpServletResponse response) {
        //数据库操作
        RecordSet rs = new RecordSet();
        //奖金金额
        String strJjje = "";
        //分公司id
        String strSubcomId = Util.null2String(request.getParameter("fgsVal"));
        //完成比
        Double douWcl = Util.getDoubleValue(request.getParameter("wclVal"));
        //分公司月任务总和
        Double douFgwrw = Util.getDoubleValue(request.getParameter("fgsrwVal")) / 10000;
        //申请时间
        String strSqsj = Util.null2String(request.getParameter("czrqVal"));
        //获取申请时间的前一个月
        //String preMonthDay = getPreMonth(strSqsj);
        String preMonthDay = strSqsj;
        //查询有效提成
        String sql = "select * from uf_sygzjlb_qyjl us,uf_jjdfs_qyjl uj,uf_jjdfs_qyjl_dt1 ujd where us.gzbh = uj.id  and uj.id =ujd.mainid " +
                "and us.fgs=? and  us.yxksrq<=? and us.yxjsrq>=?   and ujd.rwdwxx<=? and ujd.rwdwsx>?" +
                "   and ujd.wcjjdxx<=? and ujd.wcjjdsx>?";
        log.info("查询区域经理的销售金额:" + sql + ";strSubcomId:" + strSubcomId + ";preMonthDay:" + preMonthDay + ";douFgwrw:" + douFgwrw + ";douWcl:" + douWcl);
        rs.executeQuery(sql, strSubcomId, preMonthDay, preMonthDay, douFgwrw, douFgwrw, douWcl, douWcl);
        if (rs.next()) {
            strJjje = Util.null2String(rs.getString("jjjey"));

        }
        return strJjje;

    }


    /**
     * 得到办公室人员的销售提成
     *
     * @return 奖金金额
     */

    @GET
    @Path("/getOfficeTcje")
    @Produces(MediaType.TEXT_PLAIN)
    public String getOfficeTcje(@Context HttpServletRequest request, @Context HttpServletResponse response) {
        //数据库操作
        RecordSet rs = new RecordSet();
        //奖金金额
        String strJjje = "";
        //完成比
        Double douWcl = Util.getDoubleValue(request.getParameter("wclVal"));
        //规则id
        String strGzid = Util.null2String(request.getParameter("tcge"));
        //查询有效提成
        String sql = "select jjjey  from uf_jjdfs_bgsry uj,uf_jjdfs_bgsry_dt1 ud where uj.id=ud.mainid and uj.id=? and ud.wclxx<=? and ud.wclsx>?";

        log.info("查询办公室人员的销售金额:" + sql + ";douWcl:" + douWcl + ";strGzid:" + strGzid);
        rs.executeQuery(sql, strGzid, douWcl, douWcl);
        if (rs.next()) {
            strJjje = Util.null2String(rs.getString("jjjey"));

        }
        return strJjje;

    }


    /**
     * 返回日期的前一个月的1号
     */

    public String getPreMonth(String sqsjVal) {
        SimpleDateFormat format = new SimpleDateFormat("yyyy-MM");
        Calendar calendar = Calendar.getInstance();
        Date time1 = new Date();
        try {
            // 将字符串转换成日期
            time1 = format.parse(sqsjVal);
        } catch (ParseException e) {
            e.printStackTrace();
        }
        calendar.setTime(time1);
        // 设置为上一个月
        calendar.set(Calendar.MONTH, calendar.get(Calendar.MONTH) - 1);
        //申请时间的上个月
        String preMonth = format.format(calendar.getTime());
        //拼接年月日
        String preMonthDay = preMonth + "-01";
        return preMonthDay;

    }


    /**
     * 检查人员是否存在重复的
     */
    @GET
    @Path("/checkUser")
    public String checkUser(String strUserid) {
        String strReturn = "";
        return strReturn;


    }


    /**
     * 当完成比改变时,店铺奖金档方式
     *
     * @param request
     * @param response
     * @return
     */
    @GET
    @Path("/percenCompleteBonus")
    @Produces(MediaType.TEXT_PLAIN)
    public String percenCompleteBonus(@Context HttpServletRequest request, @Context HttpServletResponse response) {
        log.info("店铺奖金档计算开始");
        //数据库操作
        RecordSet rs = new RecordSet();
        RecordSet rsWcl = new RecordSet();
        //返回值
        JSONArray jsonArray = new JSONArray();
        //店铺编码
        String strDpbh = Util.null2String(request.getParameter("dpbh"));
        //完成比
        Double souWcb = Util.getDoubleValue(request.getParameter("wcbVal"), 0.0);
        //岗位编码
        String strJobCode = Util.null2String(request.getParameter("jobcode"));
        //申请时间,提率需要用前一个月去对比
        String sqsjVal = Util.null2String(request.getParameter("sqsjVal"));
        //店铺完成率
        Double douDpwcl = Util.getDoubleValue(request.getParameter("dpwcl"), 0.0);
        //月任务汇总
        Double douYrwhzVal = Util.getDoubleValue(request.getParameter("yrwhzVal"), 0.0) / 10000;
        //月任务
        Double douYrwVal = Util.getDoubleValue(request.getParameter("yrwVal"), 0.0) / 10000;
        //得到申请时间的前一个月
        // String preMonthDay = getPreMonth(sqsjVal);
        //奖金金额
        String strJjje = "";
        String preMonthDay = sqsjVal;
        //店经理个人完成率
        Double douMangeRate = 0.0;
        //首先得到店经理的个人完成率
        String sqlWcl = "select djlgrwcl from uf_jjdtcgz ux,uf_jjdtcgz_dt1 uxd,uf_dpsygzgxbjjd ud where ud.gzmc = ux.id and ux.id=uxd.mainid" +
                " and ud.dpbh=? and uxd.gwdm='01' and ud.yxksrq<=? and ud.yxjsrq>=?";
        log.info("得到店经理的个人奖金档:" + sqlWcl + ",strDpbh:" + strDpbh + ",preMonthDay:" + preMonthDay);
        rsWcl.executeQuery(sqlWcl, strDpbh, preMonthDay, preMonthDay);
        if (rsWcl.next()) {
            douMangeRate = Util.getDoubleValue(rsWcl.getString("djlgrwcl"), 0.0);
        }
        log.info("店经理的个人完成率:" + douMangeRate);
        //如果店经理的销售完成率大于规则表维护的完成率,则取店铺的销售完成率
        if (souWcb >= douMangeRate && "01".equals(strJobCode)) {
            souWcb = douDpwcl;
            douYrwVal = douYrwhzVal;
        } else if (souWcb < douMangeRate && "01".equals(strJobCode)) {
            souWcb = 0.0;
            douYrwVal = 0.0;
        }
        log.info("当前销售完成比:" + souWcb);

        //查询提率和保底金额,规则表和规则关系表联查,在有效期内且完成比在销售完成率上下线之间
        String sql = "select * from uf_jjdtcgz ux,uf_jjdtcgz_dt1 uxd,uf_dpsygzgxbjjd ud where ud.gzmc = ux.id and ux.id=uxd.mainid" +
                " and ud.dpbh=? and uxd.gwdm=? and ud.yxksrq<=? and ud.yxjsrq>=? and uxd.xsrwxx<=? and uxd.xsrwsx>? and uxd.xswclxx<=? and uxd.xswclsx>?";
        log.info("得到提率和保底金额:" + sql + ",strDpbh:" + strDpbh + ",strJobCode:" + strJobCode + ",preMonthDay:" + preMonthDay + ",souWcb:" + souWcb + ",douYrwVal:" + douYrwVal);
        rs.executeQuery(sql, strDpbh, strJobCode, preMonthDay, preMonthDay, douYrwVal, douYrwVal, souWcb, souWcb);
        if (rs.next()) {
            strJjje = Util.null2String(rs.getString("jjje"));

        }
        return strJjje;
    }

    /***
     * 零售经理提成(基数方式)
     * @param request
     * @param response
     * @return
     */
    @GET
    @Path("/retailmessNew")
    @Produces(MediaType.TEXT_PLAIN)
    public String getRetailMessNew(@Context HttpServletRequest request, @Context HttpServletResponse response) {
        log.info("零售经理提成(基数方式)数据获取===>");
        //用户id
        String strUserid = Util.null2String(request.getParameter("userid"));
        log.info("人员id:"+strUserid);
        //查询的字段名称
        String strZdmc = Util.null2String(request.getParameter("zdmc"));
        //操作日期
        String strCzrq = Util.null2String(request.getParameter("czrq"));
        //获取当前年月
        //  SimpleDateFormat format = new SimpleDateFormat("yyyy-MM");

        String strYear=strCzrq.split("-")[0];
        Integer intMonth = Integer.valueOf(strCzrq.split("-")[1]);
        String strYearMonth=strYear+String.format("%02d",intMonth);
        //是否按全地区销售业绩计算
        String strSfqdq = Util.null2String(request.getParameter("sfaqdqxsyjjs"));

        //数据库操作
        RecordSet rs = new RecordSet();
        RecordSet rsXcx = new RecordSet();
        RecordSet rs_fgs=new RecordSet();
        RecordSet rsYrw=new RecordSet();
        RecordSet rs_ndxsrw=new RecordSet();
        //分公司
        String strDpSapId = "";
        String strSqls="select DISTINCT dpsapid from uf_dpxx where ',' || lsjl || ',' LIKE '%,"+strUserid+",%'";
        log.info("获取人员所在的dpsapid的SQL:"+strSqls);
        rs_fgs.executeQuery(strSqls);
        while (rs_fgs.next()){
            strDpSapId+="'"+Util.null2String(rs_fgs.getString("dpsapid"))+"',";
        }
        log.info("获取人员在店铺信息表所属店铺sapid："+strDpSapId);
        if (strDpSapId.indexOf(",")>0){
            strDpSapId=strDpSapId.substring(0,strDpSapId.length()-1);
        }

        JSONObject json = new JSONObject();
        //下年度销售任务
        //b.yyxse + b.eyxse + b.syxse + b.syxse1 + b.wyxse + b.lyxse +
        StringBuffer strNdxsrw = new StringBuffer("SELECT SUM(xsze) xsze FROM (SELECT " +
                "a.fgs," +
                "a.nf," +
                "a.rwbh,SUM((b.yyxse+b.eyxse+b.syxse+b.syxse1+b.wyxse+b.lyxse+b.qyxse + b.byxse + b.jyxse + b.syxse2 + syyxse + b.seyxse ))AS xsze " +
                " FROM " +
                "uf_rwxdbnd a," +
                "uf_rwxdbnd_dt1 b" +
                " WHERE" +
                " a.id = b.MAINID" +
                " and b.dpbh in ("+strDpSapId+")" +
                " and a.nf='"+strYear+"'" +
                " GROUP BY a.fgs," +
                " a.nf, " +
                " a.rwbh)");
//        log.info("获取年度销售额："+strNdxsrw.toString());
        //2020-08-24:修改为获取下半年度销售额
        log.info("获取下半年度销售额："+strNdxsrw.toString());
        rs_ndxsrw.executeQuery(strNdxsrw.toString());
        //小程序总流水
        StringBuffer strXcxSql = new StringBuffer("select sum(xcxxszls) xcxxszls from uf_dpxx ud,uf_zdxcxxsyj uz where ud.dpsapid =uz.sapdpbm and  ','||ud." + strZdmc + "||','  like   '%," + strUserid + ",%' and uz.xsqj=?");
        rsXcx.executeQuery(strXcxSql.toString(),strYearMonth);
        log.info("获取小程序总流水Sql:"+"select sum(xcxxszls) xcxxszls from uf_dpxx ud,uf_zdxcxxsyj uz where ud.dpsapid =uz.sapdpbm and  ','||ud." + strZdmc + "||','  like   '%," + strUserid + ",%' and uz.xsqj='"+strYearMonth+"'");
        //区经管辖的店铺下的销售业绩汇总
        StringBuffer strDpsql = new StringBuffer("select sum(sszls) xlzls  from uf_dpxx ud,uf_xsyjhzb ux where ud.dpsapid=ux.dpbm and  ','||ud." + strZdmc + "||','  like   '%," + strUserid + ",%' and ux.sfzls='0' and ux.xzrq=?");
        rs.executeQuery(strDpsql.toString(), strYearMonth);
        log.info("获取区经管辖的店铺下的销售业绩汇总："+"select sum(sszls) xlzls  from uf_dpxx ud,uf_xsyjhzb ux where ud.dpsapid=ux.dpbm and  ','||ud." + strZdmc + "||','  like   '%," + strUserid + ",%' and ux.sfzls='0' and ux.xzrq='"+strYearMonth+"'");
        //根据人员得到所管辖的店铺及店铺的月任务
        StringBuffer strYrwsql = new StringBuffer("SELECT v.nf,v.yf,v.xzrq,SUM(v.XSRWE) as yrwhz FROM uf_dpxx ud,UF_YRWMX_VIEW v WHERE  ud.dpsapid=v.DPBH and  ','||ud." + strZdmc + "||','  like   '%," + strUserid + ",%' and v.xzrq=?  GROUP BY v.nf,v.yf,v.xzrq");
        rsYrw.executeQuery(strYrwsql.toString(),strYearMonth);
        log.info("获取店铺月任务sql:"+"SELECT v.nf,v.yf,v.xzrq,SUM(v.XSRWE) as yrwhz FROM uf_dpxx ud,UF_YRWMX_VIEW v WHERE  ud.dpsapid=v.DPBH and  ','||ud." + strZdmc + "||','  like   '%," + strUserid + ",%' and v.xzrq='"+strYearMonth+"'  GROUP BY v.nf,v.yf,v.xzrq");
        if ("1".equals(strSfqdq)) {
            //销售业绩归属人
            String strXsyjgsr = Util.null2String(request.getParameter("xsyjgsr"));
            if (!"".equals(strXsyjgsr)) {
                strDpsql.append(" and ");
                strXcxSql.append(" and ");
                String[] arrGsr = strXsyjgsr.split(",");
                for (int i = 0; i < arrGsr.length; i++) {
                    if(i==0){
                        strDpsql.append("(");
                        strXcxSql.append("(");
                    }
                    strDpsql.append(" ','|| ud.lsjl ||','like '%," + arrGsr[i] + ",%' ");
                    strXcxSql.append(" ','|| ud.lsjl ||',' like '%," + arrGsr[i] + ",%'");
                    if (i != arrGsr.length - 1) {
                        strDpsql.append(" or ");
                        strXcxSql.append(" or ");
                    } else {
                        strDpsql.append(" )");
                        strXcxSql.append(" )");
                    }

                }
            }
            log.info("销售业绩归属人:" + strXsyjgsr);

            rs.executeQuery(strDpsql.toString(), strYearMonth);

            rsXcx.executeQuery(strXcxSql.toString());

        }
        log.info("小程序总流水:"+strXcxSql.toString());
        log.info("获取销售总任务和总流水:" + strDpsql + ",strUserid:" + strUserid + ",strYearMonth:" + strYearMonth);

        if (rs.next()) {
            json.put("xlzls", Util.getDoubleValue(rs.getString("xlzls"), 0));
        }
        if(rsXcx.next()){
            json.put("xcxxszls", Util.getDoubleValue(rsXcx.getString("xcxxszls"), 0));
        }
        while (rsYrw.next()){
            json.put("yrw", Util.getDoubleValue(rsYrw.getString("yrwhz"), 0));
        }
        if (rs_ndxsrw.next()){
            json.put("ndxsrwze", Util.getDoubleValue(rs_ndxsrw.getString("xsze"), 0));
        }
        if (json != null) {
            return json.toString();
        } else {
            return "";
        }
    }

    /***
     * 零售经理提成(奖金档方式)
     * @param request
     * @param response
     * @return
     */
    @GET
    @Path("/retailManager")
    @Produces(MediaType.TEXT_PLAIN)
    public String getRetailManager(@Context HttpServletRequest request, @Context HttpServletResponse response) {
        log.info("零售经理提成(奖金档方式)数据获取===>");
        //用户id
        String strUserid = Util.null2String(request.getParameter("userid"));
        log.info("人员id:"+strUserid);
        //查询的字段名称
        String strZdmc = Util.null2String(request.getParameter("zdmc"));
        //操作日期
        String strCzrq = Util.null2String(request.getParameter("czrq"));
        String strYear=strCzrq.split("-")[0];
        Integer intMonth= Integer.valueOf(strCzrq.split("-")[1]);
        String strYearMonth=strYear+String.format("%02d",intMonth);

        //数据库操作
        RecordSet rs = new RecordSet();
        //店铺SAPID
        String strDpSapId = "";
        String strSqls="select DISTINCT dpsapid from uf_dpxx where ',' || lsjl || ',' LIKE '%,"+strUserid+",%'";
        log.info("获取人员所在的dpsapid的SQL:"+strSqls);
        rs.executeQuery(strSqls);
        while (rs.next()){
            strDpSapId+="'"+Util.null2String(rs.getString("dpsapid"))+"',";
        }
        log.info("获取人员在店铺信息表所属店铺sapid："+strDpSapId);
        if (strDpSapId.indexOf(",")>0){
            strDpSapId=strDpSapId.substring(0,strDpSapId.length()-1);
        }

        JSONObject json = new JSONObject();
        RecordSet rs_ydxsrw = new RecordSet();
        //月度销售任务
        StringBuffer strYdxsrw = new StringBuffer("SELECT sum(b.xsrwe) yrwze FROM uf_rwxdb_yd a" +
                ",UF_RWXDB_YD_DT1 b WHERE a.id=b.mainid " +
                " and b.dpbh in ("+strDpSapId+")" +
                " and a.nf='"+strYear+"'" +
                " and a.yf='"+intMonth+"'"+
                " GROUP BY a.nf,a.yf");
        log.info("获取月度销售任务Sql："+strYdxsrw.toString());
        rs_ydxsrw.executeQuery(strYdxsrw.toString());
        //区经管辖的店铺下的销售业绩汇总
        RecordSet rs_xsyj=new RecordSet();
        StringBuffer strDpsql = new StringBuffer("select sum(sszls) xlzls  from uf_dpxx ud,uf_xsyjhzb ux where ud.dpsapid=ux.dpbm and  ','||ud." + strZdmc + "||','  like   '%," + strUserid + ",%' and ux.sfzls='0' and ux.xzrq=?");
        rs_xsyj.executeQuery(strDpsql.toString(), strYearMonth);
        log.info("获取区经管辖的店铺下的销售业绩汇总："+"select sum(sszls) xlzls  from uf_dpxx ud,uf_xsyjhzb ux where ud.dpsapid=ux.dpbm and  ','||ud." + strZdmc + "||','  like   '%," + strUserid + ",%' and ux.sfzls='0' and ux.xzrq='"+strYearMonth+"'");
        //小程序总流水
        RecordSet rsXcx=new RecordSet();
        StringBuffer strXcxSql = new StringBuffer("select sum(xcxxszls) xcxxszls from uf_dpxx ud,uf_zdxcxxsyj uz where ud.dpsapid =uz.sapdpbm and  ','||ud." + strZdmc + "||','  like   '%," + strUserid + ",%' and uz.xsqj=?");
        rsXcx.executeQuery(strXcxSql.toString(),strYearMonth);
        log.info("获取小程序总流水Sql："+"select sum(xcxxszls) xcxxszls from uf_dpxx ud,uf_zdxcxxsyj uz where ud.dpsapid =uz.sapdpbm and  ','||ud." + strZdmc + "||','  like   '%," + strUserid + ",%' and uz.xsqj='"+strYearMonth+"'");

        if (rs_ydxsrw.next()) {
            //月度销售任务
            json.put("yrwze", Util.getDoubleValue(rs_ydxsrw.getString("yrwze"), 0));
        }
        if(rs_xsyj.next()){
            json.put("xlzls", Util.getDoubleValue(rs_xsyj.getString("xlzls"), 0));
        }
        while (rsXcx.next()){
            json.put("xcxxszls", Util.getDoubleValue(rsXcx.getString("xcxxszls"), 0));
        }
        if (json != null) {
            return json.toString();
        } else {
            return "";
        }
    }


    /**
     * 得到零售经理的销售提成
     *
     * @return 奖金金额
     */

    @GET
    @Path("/getAmount")
    @Produces(MediaType.TEXT_PLAIN)
    public String getAmount(@Context HttpServletRequest request, @Context HttpServletResponse response) {
        //用户id
        String strUserid = Util.null2String(request.getParameter("userid"));
        //查询的字段名称
        String strZdmc = Util.null2String(request.getParameter("zdmc"));
        //操作日期
        String strCzrq = Util.null2String(request.getParameter("czrq"));
        //获取当前年月
        //  SimpleDateFormat format = new SimpleDateFormat("yyyy-MM");
        //分公司
        String strFgsVal = Util.null2String(request.getParameter("fgsVal"));
        String strYearMonth = strCzrq.split("-")[0] + strCzrq.split("-")[1];
        //年任务
        String strNrw = Util.null2String(request.getParameter("nrw"));
        strNrw=strNrw.replace(",","");

        //数据库操作
        RecordSet rs = new RecordSet();
        JSONObject json = new JSONObject();
        //根据年任务获取金额
        StringBuffer strSql = new StringBuffer("SELECT m.GZMC,m.FGS,d.jjjey from uf_jjdfs_qyjl m,uf_jjdfs_qyjl_dt1 d WHERE m.ID=d.MAINID AND d.rwdwsx>='"+strNrw+"' AND d.rwdwxx<='"+strNrw+"' and m.fgs='"+strFgsVal+"'");
        rs.executeQuery(strSql.toString());
        log.info("根据年任务获取金额的Sql："+strSql);
        if (rs.next()) {
            json.put("jjjey", Util.getDoubleValue(rs.getString("jjjey"), 0));
            log.info("奖金金额："+rs.getString("jjjey"));
        }
        if (json != null) {
            return json.toString();
        } else {
            return "";
        }
    }
}
